﻿CREATE FUNCTION [dbo].[UpdateStringParameters]
(
	@text NVARCHAR(MAX), 
	@parameters NVARCHAR(MAX),
	@delimiter nvarchar(8) = '#',
	@parameterDelimiter nvarchar(8) = '###',
	@pairDelimiter nvarchar(8) = '##'
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @result NVARCHAR(MAX)
	
	DECLARE @t table(	
		[key] nvarchar(max) NOT NULL,
		[value] nvarchar(max)
	)
	
	INSERT INTO @t 
	SELECT LOWER([key]) [key], [value] FROM [dbo].[SplitOnPairsStringByDelimiter] (@parameters, @parameterDelimiter,@pairDelimiter)	
--------------------------------
	DECLARE @bIndex int, @eIndex int, @start int
	SET @bIndex = -1
  SET @start = 0
  DECLARE @variable nvarchar(max)
		
	DECLARE @temp table(	
		id int PRIMARY KEY,
		value nvarchar(max)
	)
	
	DECLARE @replacedText nvarchar(max)
	DECLARE @replace nvarchar(max)

	DECLARE @tempLowerVariable nvarchar(max)
  
	WHILE (@bIndex <> 0)
	BEGIN
		SET @bIndex = CHARINDEX(@delimiter , @text, @start)
		SET @eIndex = CHARINDEX(@delimiter , @text, @bIndex + 1)
		
		IF @bIndex > @eIndex OR (@bIndex = 0 AND @eIndex = 0)
		BEGIN
			BREAK
		END
		
		SET @variable = RIGHT(@text, (LEN(@text) - (@bIndex - 1 + LEN(@delimiter))))
		SET @variable = LEFT(@variable, @eIndex - @bIndex - LEN(@delimiter))
		
		SET @replacedText = @delimiter + @variable + @delimiter
		SET @replace = NULL
		
		IF CHARINDEX('?' , @variable) <> 0 AND @start < @bIndex
		BEGIN
			
			DELETE FROM @temp			
			
			INSERT INTO @temp (id, value)
			SELECT id, value FROM [dbo].[SplitStringByDelimiter] (@variable, '?')	
			
			SELECT @variable = value FROM @temp WHERE id = 1
			DECLARE @enums NVARCHAR(MAX)
			SELECT @enums = value FROM @temp WHERE id = 2
			
			SET @tempLowerVariable = LOWER(@variable)

			SELECT TOP 1 @replace = [value] FROM @t WHERE [key] = @tempLowerVariable
			SELECT @replace = [value] FROM [dbo].[SplitOnPairsStringByDelimiter] (
					@enums
					,';'
					,':'
			)
			WHERE [key] = @replace			
		END
		
		IF CHARINDEX(' ' , @variable) = 0 AND @start < @bIndex
		BEGIN
			IF @replace IS NULL
			BEGIN
				SET @tempLowerVariable = LOWER(@variable)
				SELECT TOP 1 @replace = [value] FROM @t WHERE [key] = @tempLowerVariable 
			END
			IF @replace IS NOT NULL
			BEGIN
				SET @text = REPLACE(@text, @replacedText, @replace) 
			END
			ELSE
			BEGIN
				SET @text = REPLACE(@text, @replacedText, '???') 
			END

			SET @start = 0 --@eIndex + 1
		END
		ELSE
		BEGIN
			SET @start = @bIndex + 1
		END

	END	
	
	RETURN @text
END